drop schema if exists riem;
create schema riem;
use riem;


create table department(
	dept_id char(2) primary key not null,
    dept_name varchar(100) not null,
    dean varchar(100)
);

-- create student table
create table students(
	uid char(4) primary key not null,
    uname varchar(100) not null,
    gpa double,
    did char(2),
    foreign key (did) references department (dept_id)
);



begin;
insert into department (dept_id, dept_name) values
	('01', 'Economics'),
    ('02', 'Finance and Accounting'),
    ('03', 'Managemnet');
insert into students values
	('1001', 'L.Zhang', 3.5, '03'),
    ('1002', 'Heroine', 4.0, '02'),
    ('1003', 'X. Xu', 3.5, '03'),
	('1004', 'L. Liu', 3.9, '03'),
    ('1005', 'M. Chen', 4.1, '01'),
    ('1006', 'W. Xiao', 4.2, '01');
commit;


create view view1 (uid, name, dept_name, dean ) as select uid, uname, (select dept_name from department as dep where stu.did = dep.dept_id) as department,  (select dean from department as dep where stu.did = dep.dept_id) as department  from students as stu;

select dept_name, count(uid), max(dean) as num from view1 group by dept_name;
select dept_name, count(uid), dean as num from view1 group by dept_name, dean;

-- 默认是inner
select s.uid, s.uname, d.dept_name, d.dean from students as s join department as d on s.did = d.dept_id;

create view view2 (uid, name, dept_name, dean) as select s.uid, s.uname, d.dept_name, d.dean from students as s left join department as d on s.did = d.dept_id;

select d.dept_name, d.dean, count(*) from student as s left join department as d on s.did = d.dept_id group by d.dept_id;

select any_value(uname), did from student group by did;

select d.dept_name, d.dean, c.num_students from department as d left join (select did, count(*) as num_students from students group by did) as c on d.dept_id = c.did order by c.num_students desc;

select dept_name, dean, avg_gpa from (SELECT did, avg(gpa) as avg_gpa from students group by did) as t left join department as d on t.did = d.dept_id order by avg_gpa desc;

select dept_name, case when d.dean is null then '无' else d.dean end as dean, avg_gpa from (SELECT did, avg(gpa) as avg_gpa from students group by did) as t left join department as d on t.did = d.dept_id order by avg_gpa desc;


insert into students values('1007', '张三', default, '10');
